* Extract variables required for the sectoral regressions, recompute some variables, and merge with the OECD sector level dataset

keep id ccode countryname year app year2 year80 year90 year00 D Recent yearD yearJ yearD yearJ ///
     year80D year80J  year90D year90J  year00D year00J   ///
	 lRGDP  banking sovereign restructuring
save "temp/prep_sectoral.dta", replace
use "../Database/output/Database_Sector.dta", clear
merge m:1 ccode year using "temp/prep_sectoral.dta"
keep if _merge==3
drop _merge

* recompute deva financial default restru, otherwise there will be cases of deva=1 stemming from events prior to 95
sort ccode sector year
foreach t of num 1/7{
gen app_l`t' = app[_n-`t'] if year[_n]!=year[_n-`t'] & sector[_n]==sector[_n-`t']
gen banking_l`t' = banking[_n-`t'] if year[_n]!=year[_n-`t'] & sector[_n]==sector[_n-`t']
gen sovereign_l`t' = sovereign[_n-`t'] if year[_n]!=year[_n-`t'] & sector[_n]==sector[_n-`t']
gen restructuring_l`t' = restructuring[_n-`t'] if year[_n]!=year[_n-`t'] & sector[_n]==sector[_n-`t']
}
egen deva = rowtotal(app app_l1 app_l2 app_l3 app_l4) // 4 year post deva period
egen financial = rowtotal(banking banking_l1 banking_l2 banking_l3 banking_l4)  
egen default = rowtotal(sovereign sovereign_l1 sovereign_l2 sovereign_l3 sovereign_l4) 
egen restru = rowtotal(restructuring restructuring_l1 restructuring_l2 restructuring_l3 restructuring_l4) 

* Create ratio:
gen ratio = log(importshare)

* Extract list of episodes in sample for foonote 4 in Appendix
preserve
keep if app==1
bys ccode: gen counter5 = _n
keep if counter5==1
export excel countryname year using "`1'/Appendix_Footnote4.xlsx", first(var) sh("listDevas") replace
restore
